﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using Convert.DataAccess;
using System.Configuration;
using Convert.LOTSmodel;
using Convert.CPIAMmodel;
using System.Data.OleDb;
using System.Windows;
namespace Convert.DBconverter
{
    class RegisterConverter
    {
        private static int BATCH_SAVE_SIZE = 5000;
        public static string LotsConnectionString; //= @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\LOTS\lots.mdb";//@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\LOTS\lots.mdb";//ConfigurationManager.ConnectionStrings["LOTStoCPIAMconvert.Properties.Settings.lotsConnectionString"].ConnectionString;
        public static string CpiamConnectionString; //= ConfigurationManager.ConnectionStrings["LOTStoCPIAMconvert.Properties.Settings.CPIAMPOSMaxRetailConnectionString"].ConnectionString;
        public static Dictionary<long, SalesPayment> SalePaymentDic;
        public static Dictionary<long, SalesTotalCost> SaleTotalCostDic;
        public static Dictionary<int, DebtorHeaderDetail> LotsClientIDToCPIAMDebtorHeader;
        public static Dictionary<int, Guid> ClerkIDMappings;
        public static Dictionary<int, TransactionDetail> LotsTransactionDetails;
        private static Dictionary<int, Guid> SaleIDToDebtorHeaderID;
        public static Dictionary<int, TransactionDetail> ConvertRegister(string lotsConnectionString, string cpiamConnectionString, Dictionary<int, DebtorHeaderDetail> lotsClientIDToCPIAMDebtorHeader, Dictionary<int, Guid> clerkIDMappings, out Dictionary<int, Guid> saleIDToDebtorHeaderID)
        {
            LotsConnectionString = lotsConnectionString;
            CpiamConnectionString = cpiamConnectionString;
            SaleIDToDebtorHeaderID = new Dictionary<int, Guid>();
            LotsTransactionDetails = new Dictionary<int, TransactionDetail>();
            LotsClientIDToCPIAMDebtorHeader = lotsClientIDToCPIAMDebtorHeader;
            ClerkIDMappings = clerkIDMappings;
            SaleTotalCostDic = new Dictionary<long, SalesTotalCost>();
            SalePaymentDic = new Dictionary<long,SalesPayment>();
            DataTable RegisterReceiptHeaderDT = RegisterDataTables.CreateRegisterReceiptHeaderDT();
            DataTable RegisterPaymentDT = RegisterDataTables.CreateRegisterPaymentsDT();
            OleDbConnection dbConn = new OleDbConnection(LotsConnectionString);
            try
            {
                PopulateSaleTotalCost();
                PopulateSalePayment();
                dbConn.Open();
                using (OleDbCommand command = dbConn.CreateCommand())
                {
                    command.CommandText = string.Format(@"SELECT distinct Sales.SaleID, StaffID, TimeDate, personID FROM [Sales],[SaleItem] where Sales.saleID=SaleItem.saleID");
                    OleDbDataReader reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                        Sales sales = new Sales();
                        sales.SaleID = reader.GetInt32(0);
                        sales.StaffID = reader.GetInt32(1);
                        sales.TimeDate = reader.GetDateTime(2);
                        sales.ClientID = reader.GetInt32(3);
                        if (SalePaymentDic.ContainsKey(sales.SaleID))
                        {
                            sales.Type = SalePaymentDic[sales.SaleID].Type;
                            sales.Tender = SalePaymentDic[sales.SaleID].Tender;
                            sales.BalDue = SalePaymentDic[sales.SaleID].BalDue;
                            sales.Change = SalePaymentDic[sales.SaleID].Change;
                        }
                        SaveSales(sales, RegisterReceiptHeaderDT, RegisterPaymentDT);
                        if (RegisterReceiptHeaderDT.Rows.Count % BATCH_SAVE_SIZE == 0)
                        {
                            SaveAllDataTables(CpiamConnectionString, RegisterReceiptHeaderDT, RegisterPaymentDT);
                        }
                    }
                    SaveAllDataTables(CpiamConnectionString, RegisterReceiptHeaderDT, RegisterPaymentDT);
                }
            }
            catch (Exception ex)
            {
                Logger.LogWarningAndError("Error in Convert Register ", ex);
                MessageBox.Show("Error converting Register: " + ex.Message + "\n\nStackTrace=" + ex.StackTrace);
            }
            finally
            {
                dbConn.Close();
            }
            saleIDToDebtorHeaderID = SaleIDToDebtorHeaderID;
            return LotsTransactionDetails;
        }

        private static void SaveAllDataTables(String cpiamPosMaxRetailReceiptsConnectionString, DataTable registerReceiptHeaderDT, DataTable registerPaymentDT)
        {
                if (registerReceiptHeaderDT.Rows.Count > 0)
                    dbSaveBatch.SaveBatch(registerReceiptHeaderDT, "RegisterReceiptHeader", cpiamPosMaxRetailReceiptsConnectionString);
                if (registerPaymentDT.Rows.Count > 0)
                    dbSaveBatch.SaveBatch(registerPaymentDT, "RegisterPayments", cpiamPosMaxRetailReceiptsConnectionString);
        }

        public static void SaveSales(Sales sales, DataTable RegisterReceiptHeaderDT, DataTable RegisterPaymentDT)
        {
            long lngReceiptNo = sales.SaleID;
            string lngOldReceiptNo = sales.SaleID.ToString();
            int registeringStoreID = 1;
            //string strRegNo = "ONE";
            //decimal decGSTAmt = 0;
            //decimal decTotDisc = 0;
            //decimal decRegSubTotal = 0;
            //decimal decCashOut = 0;
            //decimal decRnd = 0;
            //decimal decBalDue = 0;
            //string strPayMeth = "CA";
            //decimal decTender = 0;
            //decimal decChange = 0;
            //string strClientID = "0";
            //string strStatus = String.Empty;
            //string strVoidReason = String.Empty;
            //decimal decRegClubPtsEarned = 0;

            string strRegister = "ONE";
            Guid guidClerkID = Guid.Empty;
            string strClientNo = "0";

            string strPayMeth = null;
            string strPayMethDesc = null;

            if (sales.SaleID != 0)
            {
                if (LotsClientIDToCPIAMDebtorHeader.ContainsKey(sales.ClientID))
                {

                    strClientNo = LotsClientIDToCPIAMDebtorHeader[sales.ClientID].ClientNo.ToString() + "-" + registeringStoreID; 
                    strPayMeth = "AC";
                    strPayMethDesc = "ACCOUNT CHARGE";
                    SaleIDToDebtorHeaderID[sales.SaleID] = LotsClientIDToCPIAMDebtorHeader[sales.ClientID].DebtorHeaderID;
                }
                else
                {
                    strClientNo = "REMOVED";
                    strPayMeth = "AC";
                    strPayMethDesc = "ACCOUNT CHARGE";
                }
            }


            if (sales.StaffID != 0)
            {
                if (ClerkIDMappings.ContainsKey(sales.StaffID))
                {
                    guidClerkID = ClerkIDMappings[sales.StaffID];
                }
                else if (sales.AccountID != 0)
                {
                    guidClerkID = Guid.Empty;
                    //strPayMeth = "AC";
                }
                else
                {
                    //not an auto charge item either - something wrong here
                    //Logger.Warn("Transaction does not have a clerk ID or a Customer ID: " + lngReceiptNo);
                    guidClerkID = Guid.NewGuid();
                    //return; // do not save it
                }
            }
            else
            {
                //Logger.Warn("Transaction does not have a cashier id or Transaction Number: " + lngReceiptNo);
                guidClerkID = Guid.NewGuid();
                //return; // do not save it
            }



            if (sales.Type==1)
            {
                strPayMeth = "CA";
                strPayMethDesc = "CASH";
            }
            else if (sales.Type == 2)
            {
                strPayMeth = "EP";
                strPayMethDesc = "EFTPOS";
            }
            else if (sales.Type == 3)
            {
                strPayMeth = "CQ";
                strPayMethDesc = "Cheque";  
            }
            else if (sales.Type == 4)
            {
                strPayMeth = "CC";
                strPayMethDesc = "Credit Card"; 
            }
            else if (sales.Type == 5)
            {
                strPayMeth = "OT";
                strPayMethDesc = "OTHER";
            }
            else if (sales.Type == 6)
            {
                strPayMeth = "AC";
                strPayMethDesc = "Account Charge";
            }
            else
            {
                strPayMeth = "CA";
                strPayMethDesc = "CASH";
            }

            decimal decTender = 0;
            decimal decChange = 0;
            decimal decRounding = 0;
            decimal decGSTAmt = 0; 
            decimal decTotDisc = 0;
            decimal decSubTotal = 0;
            decimal decBalDue = 0;

            decTender = sales.Tender/100;
            decChange = sales.Change/100*-1;
            decSubTotal = sales.BalDue/100;
            decBalDue = sales.BalDue/100;
            decGSTAmt = (decimal) (SaleTotalCostDic[sales.SaleID].Tax / 100);
            decTotDisc = (decimal) (SaleTotalCostDic[sales.SaleID].Disc / 100);
            int iCnt = (int) SaleTotalCostDic[sales.SaleID].Quantity;
            string strStatus = "Active";
            string strVoidReason = String.Empty;
            DateTime dteReceiptTime = sales.TimeDate;

            int intRegTillUpID = -1;

            RegisterReceiptHeader registerReceiptHeader = new RegisterReceiptHeader(lngReceiptNo, lngOldReceiptNo, registeringStoreID, dteReceiptTime, strRegister, guidClerkID, iCnt, strStatus, decGSTAmt, decTotDisc,
                                              decSubTotal, 0, decRounding, decBalDue, strPayMeth, decTender, decChange, strClientNo, intRegTillUpID, strVoidReason);
            RegisterReceiptHeader.SaveToDateTable(registerReceiptHeader, RegisterReceiptHeaderDT);

            RegisterPayments registerPayment = new RegisterPayments(lngReceiptNo, registeringStoreID, strPayMethDesc, strPayMeth, decBalDue, dteReceiptTime, strClientNo, -1, Guid.Empty);
            RegisterPayments.SaveToDateTable(registerPayment, RegisterPaymentDT);

            TransactionDetail detail = new TransactionDetail();
            detail.ClerkID = guidClerkID;
            detail.StrClientNo = strClientNo;
            detail.Date = dteReceiptTime;
            LotsTransactionDetails[sales.SaleID] = detail;
        }

        public static void PopulateSalePayment()
        {
            OleDbConnection dbConn = new OleDbConnection(LotsConnectionString);
            try
            {
                dbConn.Open();
                using (OleDbCommand command = dbConn.CreateCommand())
                {
                    command.CommandText = string.Format(@"SELECT SaleID, Type, Value, FrozenBalance, AccountID FROM SalePayment");
                    OleDbDataReader reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                        int SaleID = reader.GetInt32(0);
                        int Type = reader.GetInt32(1);
                        int Tender = reader.GetInt32(2);
                        int BalDue = reader.GetInt32(2);
                        int FrozenBalance = reader.GetInt32(3);
                        int AccountID = reader.GetInt32(4);
                        if (!SalePaymentDic.ContainsKey(reader.GetInt32(0)))
                        {
                            SalesPayment salesPayment = new SalesPayment();
                            salesPayment.SaleID = SaleID;
                            salesPayment.Type = Type;
                            salesPayment.Tender = Tender;
                            salesPayment.BalDue = BalDue;
                            salesPayment.AccountID = AccountID;
                            salesPayment.Change = 0;
                            SalePaymentDic[salesPayment.SaleID] = salesPayment;
                            
                            if (Type == 6)
                            {
                                if (SaleTotalCostDic.ContainsKey(SaleID))
                                {
                                    salesPayment.Tender = (decimal) SaleTotalCostDic[SaleID].Price;
                                    salesPayment.BalDue = (decimal) SaleTotalCostDic[SaleID].Price;
                                }
                            }
                        }
                        else
                        {
                            if (Type == 0)
                            {
                                SalePaymentDic[SaleID].BalDue = FrozenBalance;
                                SalePaymentDic[SaleID].Change = BalDue;
                            }
                        }
                    }
                }
            }
            finally
            {
                dbConn.Close();
            }
        }

        public static void PopulateSaleTotalCost()
        {
            OleDbConnection dbConn = new OleDbConnection(LotsConnectionString);
            try
            {
                dbConn.Open();
                using (OleDbCommand command = dbConn.CreateCommand())
                {
                    command.CommandText = string.Format(@"SELECT SaleID, SUM(Retail*Quantity) AS Total, SUM(Tax*Quantity) AS TaxAmount, SUM(Discount*Quantity) AS DiscAmount, COUNT(SaleItemID) AS ItemCount
                                                            FROM SaleItem GROUP BY SaleID");
                    OleDbDataReader reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                        SalesTotalCost salesTotalCost = new SalesTotalCost();
                        int SaleID = reader.GetInt32(0);
                        salesTotalCost.Price = reader.IsDBNull(1)? 0 : reader.GetDouble(1);
                        salesTotalCost.Tax = reader.GetDouble(2);
                        salesTotalCost.Disc = reader.GetDouble(3);
                        salesTotalCost.Quantity = reader.GetInt32(4);
                        SaleTotalCostDic[SaleID] = salesTotalCost;
                    }
                }
            }
            finally
            {
                dbConn.Close();
            }
        }
    }
}
